package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.Goods;

import utils.JdbcUtils;

public class GoodsDao {

	// {1}增加
	public void addGoods(Goods goods) throws SQLException {
		String sql = "insert into goods(gid,gname,amount,info,oldprice,price,datatime,sale,photo,grade,lable)values(?,?,?,?,?,?,?,?,?,?,?)";// ?表示占位符，填充插入占位符时表中有几列就写几
		int row = JdbcUtils.update(sql, goods.getGid(), goods.getGname(), goods.getAmount(), goods.getInfo(),
				goods.getOldprice(), goods.getPrice(), goods.getDatatime(), goods.getSale(), goods.getPhoto(),
				goods.getGrade(), goods.getLable());
		System.out.println("已增加" + row + "行数据");
	}

	// {2}删除
	public void deleteGoods(Goods goods) throws SQLException {
		String sql = "delete from goods where gid=?";
		int row = JdbcUtils.update(sql, goods.getGid());
		System.out.println("已删除" + row + "行数据");
	}

	// {3}修改
	public void updateGoods(Goods goods) throws SQLException {
		String sql = "update goods set gname=?,amount=?,info=?,oldprice=?,price=?,datatime=?,sale=?,photo=?,grade=?,lable=? where gid=?";
		int row = JdbcUtils.update(sql, goods.getGname(), goods.getAmount(), goods.getInfo(),
				goods.getOldprice(), goods.getPrice(), goods.getDatatime(), goods.getSale(), goods.getPhoto(),
				goods.getGrade(), goods.getLable(), goods.getGid());
		System.out.println("已修改" + row + "行数据");
	}

	// {4}根据商品ID查询
	public Goods queryById(Goods g) throws SQLException {
		String sql = "select * from goods g where g.gid=?";
		Goods goodre = JdbcUtils.query(sql, Goods.class, g.getGid());
		return goodre;
	}

	// {5}查询所有记录
	public List<Goods> queryAll() throws SQLException {
		List<Goods> list = null;
		String sql = "select * from goods g where g.gid <= ?";
		list = JdbcUtils.queryList(sql, Goods.class, 1000);
		return list;
	}

	// {6}根据商品ID查询图片
	public List<String> querByIdPic(Goods goods) throws SQLException {
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		Statement stmt;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/nypro?useSSL=false&serverTimezone=UTC&useSSL=false", "root", "0487");
			String sql = "select psrc from \r\n" + "goods_picture_info as pd \r\n" + "join image on\r\n"
					+ "pd.gpId = image.pictureId\r\n" + "where pd.gid = " + goods.getGid() + ";";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				list.add(rs.getString("psrc"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	// {7}根据商品ID查询标签
	public List<String> quaryByIdLabel(Goods goods) throws SQLException {
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		Statement stmt;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/nypro?useSSL=false&serverTimezone=UTC&useSSL=false", "root", "0487");
			String sql = "select label_name from \r\n" + "label as pd \r\n" + "join goods_label_info on\r\n"
					+ "goods_label_info.glId = pd.labelId\r\n" + "where goods_label_info.gid = " + goods.getGid()
					+ ";";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			System.out.println(rs);
			while (rs.next()) {
				list.add(rs.getString("label_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	// {8}根据商品ID查询类别
	public List<String> quaryByIdCategory(Goods goods) throws SQLException {
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		Statement stmt;
		ResultSet rs = null; // g->c
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/nypro?useSSL=false&serverTimezone=UTC&useSSL=false", "root", "0487");
			String sql = "select category_name from \r\n" + "goods_category_info as pd \r\n" + "join category on\r\n"
					+ "pd.gcId = category.categoryId\r\n" + "where pd.gid = " + goods.getGid() + ";";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			System.out.println(rs);
			while (rs.next()) {
				list.add(rs.getString("category_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//{9}分页查询
	public List<Goods> queryAllByPage(int newPage,int pageNum ) throws SQLException {
		int pageStart = (newPage-1)*pageNum;
		int pageEnd = newPage*pageNum;
		String sql = "select * from goods limit ?,?";
		List<Goods> list = JdbcUtils.queryList(sql, Goods.class, pageStart, pageNum);
		return list;
	}
	
	//{10}根据购物车表用户Id获得商品列表
	public List<Goods> querByuIdGoods(int uid) throws SQLException{
		List<Goods> list = null;
		String sql = "select goods.gid,gname,amount,info,oldprice,price,datatime,photo,grade,lable,sale "
						+ "from cart as pd join goods on pd.gid = goods.gid where pd.uid = ?";		
		list = JdbcUtils.queryList(sql, Goods.class,uid);

		return list;
	}
	//{11}模糊查询
	public List<Goods> querBylikeGoods(String like) throws SQLException{
		List<Goods> list = null;
//		int pageStart = (newPage-1)*pageNum;
//		int pageEnd = newPage*pageNum;
		String name="'%";
		for(int i = 0; i < like.length(); i++) {
			name += like.charAt(i)+"%";
		}
		name += "'";
		String sql = "select * from goods where gname like " + name;
		list = JdbcUtils.queryList(sql, Goods.class);

		return list;
	}

	//{12}通过Lable标签ID去查询商品列表
	public List<Goods> querBylIdGoods(int lid) throws SQLException{
		List<Goods> list = null;
		String sql = "select goods.gid,gname,amount,info,oldprice,price,datatime,photo,grade,lable,sale "
						+ "from goods_lable_info "
						+ "as pd join goods on pd.gid = goods.gid where pd.lid = ?";		
		list = JdbcUtils.queryList(sql, Goods.class,lid);

		return list;
	}
	


}
